﻿using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using XZTZ.UMP.Public.Common;

namespace XZTZ.UMP.WEB.Data
{
    public static class ExtendDBContext
    {
        private static IEnumerable<T> Execute<T>(IdentityDbContext db, string sql, CommandType type, SqlParameter[] sqlParams) where T : new()
        {
            DbConnection connection = db.Database.GetDbConnection();
            DbCommand cmd = connection.CreateCommand();
            db.Database.OpenConnection();
            cmd.CommandText = sql;
            cmd.CommandType = type;
            if (sqlParams != null)
            {
                cmd.Parameters.AddRange(sqlParams);
            }
            DataTable dt = new DataTable();
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                dt.Load(reader);
            }
            db.Database.CloseConnection();
            return dt.ToCollection<T>();
        }

        private static DataSet Execute(IdentityDbContext db, string sql, CommandType type, SqlParameter[] sqlParams)
        {
            DbConnection connection = db.Database.GetDbConnection();
            DbCommand cmd = connection.CreateCommand();
            db.Database.OpenConnection();
            cmd.CommandText = sql;
            cmd.CommandType = type;
            if (sqlParams != null)
            {
                cmd.Parameters.AddRange(sqlParams);
            }
            DataSet dt = null;
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                dt = ConvertDataReaderToDataSet(reader);
            }
            db.Database.CloseConnection();
            return dt;
        }

        private static DataSet ConvertDataReaderToDataSet(DbDataReader reader)
        {
            DataSet dataSet = new DataSet();
            do
            {
                // Create new data table
                DataTable schemaTable = reader.GetSchemaTable();
                DataTable dataTable = new DataTable();
                if (schemaTable != null)
                {
                    // A query returning records was executed 
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        DataRow dataRow = schemaTable.Rows[i];
                        // Create a column name that is unique in the data table 
                        string columnName = (string)dataRow["ColumnName"]; //+ " // Add the column definition to the data table 
                        DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
                        dataTable.Columns.Add(column);
                    }
                    dataSet.Tables.Add(dataTable);
                    // Fill the data table we just created
                    while (reader.Read())
                    {
                        DataRow dataRow = dataTable.NewRow();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            dataRow[i] = reader.GetValue(i);
                        }
                        dataTable.Rows.Add(dataRow);
                    }
                }
                else
                {
                    // No records were returned
                    DataColumn column = new DataColumn("RowsAffected");
                    dataTable.Columns.Add(column);
                    dataSet.Tables.Add(dataTable);
                    DataRow dataRow = dataTable.NewRow();
                    dataRow[0] = reader.RecordsAffected;
                    dataTable.Rows.Add(dataRow);
                }
            }
            while (reader.NextResult());
            return dataSet;
        }

        private static int ExecuteNoQuery<T>(IdentityDbContext db, string sql, SqlParameter[] sqlParams) where T : new()
        {
            DbConnection connection = db.Database.GetDbConnection();
            DbCommand cmd = connection.CreateCommand();
            int result = 0;
            db.Database.OpenConnection();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            if (sqlParams != null)
            {
                cmd.Parameters.AddRange(sqlParams);
            }
            result = cmd.ExecuteNonQuery();
            db.Database.CloseConnection();
            return result;
        }

        public static IEnumerable<T> ExecSqlReader<T>(IdentityDbContext db, string sql, SqlParameter[] sqlParams = null) where T : new()
        {
            return Execute<T>(db, sql, CommandType.Text, sqlParams);
        }

        /// <summary>
        /// 执行SQL返回受影响的行数
        /// </summary>
        public static int ExecSqlNoQuery<T>(IdentityDbContext db, string sql, SqlParameter[] sqlParams = null) where T : new()
        {
            return ExecuteNoQuery<T>(db, sql, sqlParams);
        }
        /// <summary>
        /// 执行存储过程返回IEnumerable数据集
        /// </summary>
        public static IEnumerable<T> ExecProcReader<T>(IdentityDbContext db, string sql, SqlParameter[] sqlParams = null) where T : new()
        {
            return Execute<T>(db, sql, CommandType.StoredProcedure, sqlParams);
        }

        public static DataSet ExecProcReader(IdentityDbContext db, string sql, SqlParameter[] sqlParams = null) 
        {
            return Execute(db, sql, CommandType.StoredProcedure, sqlParams);
        }
    }
}
